﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace PADWH35
{
    public partial class BlowJobStatusFunction : Form
    {
        public BlowJobStatusFunction()
        {
            InitializeComponent();
            this.Font = new Font(this.Font.Name, 12);
            datagv.AutoGenerateColumns = false;
            tableLayoutPanel1.Dock = DockStyle.Fill;
            datagv.Dock = DockStyle.Fill;
            tableLayoutPanel2.Dock = DockStyle.Fill;
        }

        private void button1_Click(object sender, EventArgs e)//查询
        {
            BindData();
        }

        private void button4_Click(object sender, EventArgs e)//保存
        {
            SaveData();

            int FDSR = datagv.FirstDisplayedScrollingRowIndex;
            int FDSC = datagv.FirstDisplayedScrollingColumnIndex;
            BindData();
            datagv.FirstDisplayedScrollingRowIndex = FDSR;
            datagv.FirstDisplayedScrollingColumnIndex = FDSC;
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            checkBox2.Enabled = !(Boolean)checkBox1.Checked;
            checkBox3.Enabled = !(Boolean)checkBox1.Checked;
            checkBox4.Enabled = !(Boolean)checkBox1.Checked;
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            this.label2.Enabled = (textBox1.Text == "");
            this.sdate.Enabled = (textBox1.Text == "");
            this.edate.Enabled = (textBox1.Text == "");
        }

        private void datagv_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == datagv.Columns["IsStartProdCol"].Index || e.ColumnIndex == datagv.Columns["stopCol"].Index)
            {
                datagv.Rows[e.RowIndex].Cells["IsChangedCol"].Value = true;
            }
        }

        public void BindData()
        {
            /*
            string sql = "select a.JobNum,a.PartNum,d.OpDesc,a.PartQty,a.ReceivedQty,isnull(f.PartQty,0) LaborQty,c.ReqDueDate,c.StartDate,c.DueDate,isnull(cast(d.Date01 as nvarchar) +' '+ CONVERT(varchar(12) , CAST(dateadd(ss, d.Number03, 0)  as time),108),CONVERT(varchar(16), GETDATE(), 20)) ActualDate,e.partnum partnum1,e.description,e.ium,e.QtyPer,e.RequiredQty,e.IssuedQty,isnull(pt.tranqty,0) ReturnQty,cast(d.StartDate as nvarchar) +' '+ CONVERT(varchar(12) , CAST(dateadd(ss, d.StartHour, 0)  as time),108) ScheduleDate,c.CheckBox05 stop,d.AssemblySeq,d.OprSeq from erp.jobpart a with(nolock)"
                         + " left join erp.JobAsmbl b with(nolock) on a.company = b.company and a.jobnum = b.jobnum and a.PartNum = b.PartNum"
                         + "  inner join jobhead c with(nolock) on a.Company = c.Company and a.JobNum = c.JobNum and a.PartNum = c.PartNum"
                         + " left join JobOper d with(nolock) on a.company = d.company  and d.jobnum = a.jobnum and b.AssemblySeq = d.AssemblySeq"
                         + " left join JobMtl e with(nolock) on a.company = e.company and d.jobnum = e.jobnum and d.AssemblySeq = e.AssemblySeq and d.oprseq = e.RelatedOperation"
                         + " left join (select a.Company, a.JobNum, a.AssemblySeq, a.OprSeq, sum(PartQty)PartQty, b.PartNum from LaborDtl a with(nolock)"
                         + " left join  erp.laborpart b with(nolock) on a.Company = b.Company and a.LaborHedSeq = b.LaborHedSeq and a.LaborDtlSeq = b.LaborDtlSeq"
                         + "  where a.company = 'NSZ' group by a.Company, a.JobNum, a.AssemblySeq, a.OprSeq, b.PartNum) f on d.Company = f.Company and d.JobNum = f.JobNum and a.PartNum = f.PartNum and d.AssemblySeq = f.AssemblySeq and d.OprSeq = f.OprSeq"
                         + "  left join (select company, jobnum, AssemblySeq, JobSeq, partnum, sum(tranqty)tranqty from erp.PartTran with(nolock) where TranType = 'STK-MTL' and tranqty < 0 group by company, jobnum, AssemblySeq, jobseq, partnum) pt on e.company = pt.company and e.jobnum = pt.jobnum and e.AssemblySeq = pt.AssemblySeq and e.mtlseq = pt.jobseq and e.partnum = pt.partnum"
                         + "  where a.company='NSZ' and (a.jobnum='" + textBox1.Text + "' or ''='" + textBox1.Text + "') and c.CreateDate>='" + sdate.Value.ToString("yyyy-MM-dd") + "' and c.CreateDate<='" + edate.Value.ToString("yyyy-MM-dd") + "'   ";
            */
            string sql = string.Empty;
            sql += "SELECT JH.JobNum ,JA.PartNum ,CAST(JO.OprSeq AS VARCHAR(9))+' '+JO.OpDesc OpDesc ,JO.ShortChar01 ,JO.ShortChar02 ,JP.PartQty ,JP.ReceivedQty ,ISNULL(LABOR.LaborQty, 0) LaborQty ,CASE WHEN JP.PartQty - JP.ReceivedQty < 0 THEN 0 ELSE JP.PartQty - JP.ReceivedQty END UnProd ,JH.ReqDueDate ," + Environment.NewLine +
                   "    CAST(JO.StartDate AS NVARCHAR)/*+' '+CONVERT(VARCHAR(12),CAST(DATEADD(SS,JO.StartHour,0)AS TIME),108)*/ StartDate ,JH.DueDate ," + Environment.NewLine +
                   "    CAST(JO.Date01 AS NVARCHAR) /*+ ' ' + CONVERT(VARCHAR(12), CAST(DATEADD(SS, JO.Number03, 0) AS TIME), 108)*/ AS ActualDate ,CASE WHEN JO.Date01 IS NULL THEN 0 ELSE 1 END AS IsStartProd ," + Environment.NewLine +
                   "    JH.CheckBox05 Stop, JA.AssemblySeq ,JO.OprSeq--,JM.PartNum PartNum1, JM.Description ,JM.IUM ,JM.QtyPer ,JM.RequiredQty ,JM.IssuedQty ,CASE WHEN JM.RequiredQty - JM.IssuedQty < 0 THEN 0 ELSE JM.RequiredQty - JM.IssuedQty END UnIssuedQty ,ISNULL(PT.TranQty, 0) ReturnQty" + Environment.NewLine +
                   "FROM JobHead JH" + Environment.NewLine +
                   "    INNER JOIN ERP.JobAsmbl JA ON JH.Company = JA.Company AND JH.JobNum = JA.JobNum" + Environment.NewLine +
                   "        AND JH.JobReleased = 1" + Environment.NewLine +
                   "        AND LEFT(JH.ShortChar06,6) IN('Normal','Re-wor')" + Environment.NewLine +
                   "        AND JH.Company = 'NSZ'" + Environment.NewLine +
                   "        AND(JH.JobNum = '" + textBox1.Text + "' or '' = '" + textBox1.Text + "')" + Environment.NewLine;
            if (sdate.Enabled) { sql += "        AND JH.CreateDate BETWEEN '" + sdate.Value.ToString("yyyy-MM-dd") + "' AND '" + edate.Value.ToString("yyyy-MM-dd") + "'" + Environment.NewLine; }
            sql += "    INNER JOIN ERP.JobPart JP ON JH.Company = JP.Company AND JH.JobNum = JP.JobNum AND JH.PartNum = JP.PartNum AND JP.JobClosed = 0 AND JP.PartQty > JP.ReceivedQty" + Environment.NewLine +
                   "    LEFT JOIN JobOper JO ON JA.Company = JO.Company AND JA.JobNum = JO.JobNum AND JA.AssemblySeq = JO.AssemblySeq" + Environment.NewLine +
                   "    --LEFT JOIN JobMtl JM ON JO.Company = JM.Company AND JO.JobNum = JM.JobNum AND JA.AssemblySeq = JM.AssemblySeq AND JO.OprSeq = JM.RelatedOperation" + Environment.NewLine +
                   "    LEFT JOIN (SELECT Company ,JobNum ,AssemblySeq ,OprSeq ,SUM(LaborQty)LaborQty FROM LaborDtl WITH(NOLOCK)WHERE company = 'NSZ' GROUP BY Company ,JobNum ,AssemblySeq ,OprSeq) LABOR" + Environment.NewLine +
                   "        ON JO.Company = LABOR.Company AND JO.JobNum = LABOR.JobNum AND JO.AssemblySeq = LABOR.AssemblySeq AND JO.OprSeq = LABOR.OprSeq" + Environment.NewLine +
                   "    --LEFT JOIN (SELECT Company, JobNum, AssemblySeq, JobSeq, PartNum, SUM(TranQty)TranQty FROM Erp.PartTran WITH(NOLOCK) WHERE TranType = 'STK-MTL' AND TranQty < 0 GROUP BY Company, JobNum, AssemblySeq, JobSeq, PartNum) PT" + Environment.NewLine +
                   "    --    ON JM.Company = PT.Company AND JM.JobNum = PT.JobNum AND JM.AssemblySeq = PT.AssemblySeq AND JM.mtlseq = PT.JobSeq AND JM.PartNum = PT.PartNum" + Environment.NewLine +
                   "WHERE 1=1" + Environment.NewLine +
                   "    --AND JO.Date01 <= GETDATE()" + Environment.NewLine;
            if (!checkBox1.Checked)
            {
                string sql_IN = string.Empty;
                if (checkBox2.Checked) { sql_IN += ",'BMM'"; }//吹塑
                if (checkBox3.Checked) { sql_IN += ",'JSM'"; }//注塑
                if (checkBox4.Checked) { sql_IN += ",'ASM'"; }//后加工
                if (!sql_IN.Equals(string.Empty))
                {
                    sql += "    AND LEFT(JO.OpCode ,3) IN(''" + sql_IN + ")" + Environment.NewLine;
                }
            }
            sql += "ORDER BY JH.JobNum ,JA.AssemblySeq ,JO.OprSeq";

            //Clipboard.Clear();
            //Clipboard.SetText(sql);

            DataTable dt = DBHelper.ExecuteDt(sql);
            datagv.DataSource = dt;
        }

        private bool SaveData()
        {
            string sql;
            int iCount = 0;
            for (int i = 0; i < datagv.Rows.Count; i++)
            {
                if (datagv.Rows[i].Cells["IsChangedCol"].Value != null && (Boolean)datagv.Rows[i].Cells["IsChangedCol"].FormattedValue)
                {
                    //if(日期=空 && 开工=√) 开工
                    //if(日期=空 && 开工=  ) 不做事
                    //if(日期=有 && 开工=√) 不做事
                    //if(日期=有 && 开式=  ) 去掉日期
                    sql = string.Empty;
                    if (datagv.Rows[i].Cells["ActualDateCol"].Value.ToString() == "" && (Boolean)datagv.Rows[i].Cells["IsStartProdCol"].FormattedValue)
                    {
                        sql += "UPDATE JobOper SET Date01=GETDATE(),Number03=DATEPART(HOUR,GETDATE())*3600 + DATEPART(Minute,GETDATE())*60 + DATEPART(SECOND,GETDATE()) WHERE Company = 'NSZ' AND JobNum='" + datagv[JobNumCol.Index, i].Value.ToString() + "' AND AssemblySeq='" + datagv[AssemblySeqCol.Index, i].Value.ToString() + "' AND OprSeq='" + datagv[OprSeqCol.Index, i].Value.ToString() + "';" + Environment.NewLine;
                    }
                    if (datagv.Rows[i].Cells["ActualDateCol"].Value.ToString() != "" && (Boolean)datagv.Rows[i].Cells["IsStartProdCol"].FormattedValue == false)
                    {
                        sql += "UPDATE JobOper SET Date01=NULL,Number03=0.00 WHERE Company = 'NSZ' AND JobNum='" + datagv[JobNumCol.Index, i].Value.ToString() + "' AND AssemblySeq='" + datagv[AssemblySeqCol.Index, i].Value.ToString() + "' AND OprSeq='" + datagv[OprSeqCol.Index, i].Value.ToString() + "';" + Environment.NewLine;
                    }

                    //暂停
                    sql += "UPDATE JobHead SET CheckBox05='" + datagv[stopCol.Index, i].Value + "' WHERE Company = 'NSZ' AND JobNum='" + datagv[JobNumCol.Index, i].Value + "';";

                    DBHelper.ExecuteSql(sql);
                    //MessageBox.Show(sql);

                    datagv.Rows[i].Cells["IsChangedCol"].Value = false;
                    iCount++;
                }
            }
            if (iCount != 0) { MessageBox.Show("更新成功,共" + iCount.ToString() + "行."); }
            return true;
        }
    }
}